package com.szholly.pro.watergas_server.ps.schedule;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;

import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.RowBase;
import com.szholly.plug.form.excel.utils.StringUtil;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.common.data.DbManager;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;

import lrq.data.DataTable;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class SaveAccessAlarm implements Job {
	@Override
	public void execute(JobExecutionContext arg0) throws JobExecutionException {
		execute();
	}
	public void execute(){
		//第一步从oracle数据库获取门禁报警信息最新一条数据
		String sql=" select * from (select * from ACCESS_ALARM_INFO order by F_READDATE desc) where rownum = 1 ";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		String time="";
		if(rows.size()>0){
			RowBase row=rows.get(0);
			 time=row.getSafeString("F_READDATE");
		}
		else{
			return;
		}
		//第二步根据时间从sqlserver数据库获取未保存到oracle报警信息
		String sqld=" SELECT t_b_station.STATION_CODE ,t_b_station.STATION_NAME AS '站点名称',t_d_SwipeRecord.f_RecID AS '序号',  t_d_SwipeRecord.f_CardNO AS '卡号',   '' AS '工号', '' AS '姓名', '' AS '部门', "
				+ "CONVERT(varchar(100), t_d_SwipeRecord.f_ReadDate, 20) AS '时间', t_b_Reader.f_ReaderName  AS '地点',t_b_Door.f_DoorName  AS '门名称',CASE WHEN t_d_SwipeRecord.f_Character =1 THEN '允许通过' ELSE '禁止通过' END  AS '通过',"
                +"t_d_SwipeRecord.f_RecordAll AS '原始数据' FROM ((t_d_SwipeRecord  LEFT JOIN   t_b_Reader on ( t_b_Reader.f_ReaderID = t_d_SwipeRecord.f_ReaderID) ) "
                +" LEFT JOIN t_b_Door ON (t_b_Door.f_ControllerID = t_b_Reader.f_ControllerID "
               +" and ((f_ControllerSN>400000000 and t_b_Door.f_DoorNO = t_b_Reader.f_ReaderNO) "
               +" or (f_ControllerSN<400000000 and (((t_b_Door.f_DoorNO*2) = t_b_Reader.f_ReaderNO) "
               +" or ((t_b_Door.f_DoorNO*2-1) = t_b_Reader.f_ReaderNO))))) "
               +"   LEFT JOIN t_b_station ON t_d_SwipeRecord.f_ControllerSN=t_b_station.f_ControllerSN)"
               +" WHERE (t_d_SwipeRecord.f_CardNO =84 or t_d_SwipeRecord.f_CardNO =85 or t_d_SwipeRecord.f_CardNO=9) and ( ((((f_RecOption - (f_RecOption % 2)) / 2) % 2) =1)) ";
		if(StringUtil.IsNotNullOrEmpty(time)){
			sqld+= " and t_d_SwipeRecord.f_ReadDate > CONVERT(datetime,'"+time+"', 120)";
		}
		sqld+=" order by 时间  asc";
		try{
			DataTable dt= DbManager.getInstance().getMsSqlExecuter().executeQuery(sqld);
			if(dt.getRecordCount()>0){
				Object json=dt.toJson();
				JSONArray ja=JSONArray.fromObject(json);
				for(int i=0;i<ja.size();i++){
					JSONObject obj = (JSONObject) ja.get(i);
					String STATION_CODE=obj.get("station_code").toString();
					String STATION_NAME=obj.get("站点名称").toString();
					String F_RECID=obj.get("序号").toString();
					String F_CARDNO=obj.get("卡号").toString();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");  
				    Date F_READDATE = sdf.parse(obj.get("时间").toString()); 
					String IS_LOOK="0";
					String GUID = UUID.randomUUID().toString();
					//供弹框使用
					String savesql="insert into ACCESS_ALARM_INFO(GUID,STATION_CODE,STATION_NAME,F_RECID,F_CARDNO,F_READDATE,IS_LOOK)values(@GUID,@STATION_CODE,@STATION_NAME,@F_RECID,@F_CARDNO,@F_READDATE,@IS_LOOK)";
					parms.clear();
					parms.add(new GlobalParameter("GUID", GUID));
					parms.add(new GlobalParameter("STATION_CODE", STATION_CODE));
					parms.add(new GlobalParameter("STATION_NAME", STATION_NAME));
					parms.add(new GlobalParameter("F_RECID", F_RECID));
					parms.add(new GlobalParameter("F_CARDNO", F_CARDNO));
					parms.add(new GlobalParameter("F_READDATE", F_READDATE));
					parms.add(new GlobalParameter("IS_LOOK", IS_LOOK));
					DataUtil.ExecuteUpdateSql(savesql, parms);
					
					parms.clear();
					String updatetime=" select * from (select * from OP_ALAR_INFO where type='5' and IS_RECOVER='0'  and STATION_CODE='"+STATION_CODE+"' order by START_TIME desc) where rownum = 1 ";
					List<RowBase> data = DataUtil.getSelectRows(updatetime, parms);
					//判断门关闭就去更新状态
					if(F_CARDNO.equals("9")){
						if(data.size()>0){
							RowBase rowe=data.get(0);
							 String id=rowe.getSafeString("GUID");
							 String upstatus=" update OP_ALAR_INFO set IS_RECOVER='1' where GUID='"+id+"'";
							 DataUtil.ExecuteUpdateSql(upstatus, parms);
							 String code=rowe.getSafeString("STATION_CODE");
							 String upalarm="update ACCESS_ALARM_INFO set is_look='1' where  STATION_CODE='"+code+"'";
							 DataUtil.ExecuteUpdateSql(upalarm, parms);
						}
						parms.clear();
						String timesql=" select * from OP_ALAR_INFO where type='5' and IS_RECOVER='0'  order by START_TIME desc ";
						List<RowBase> dataw = DataUtil.getSelectRows(timesql, parms);
						//更新时间
						if(dataw.size()>0){
							for(int m=0;m<dataw.size();m++){
								//更新时长
								RowBase roww=dataw.get(m);
								 String id=roww.getSafeString("GUID");
								 String stime=roww.getSafeString("CONTINUE_TIME");
								 int n=Integer.parseInt(stime)+10;
								 String timestr=n+"";
								 String uptime=" update OP_ALAR_INFO set CONTINUE_TIME='"+timestr+"' where GUID='"+id+"'";
								 DataUtil.ExecuteUpdateSql(uptime, parms);
							}
						}
					}
					else{
						//判断如果该站点已经有报警数据就去更改时间
						if(data.size()>0){
							parms.clear();
							String timesql=" select * from OP_ALAR_INFO where type='5' and IS_RECOVER='0'  order by START_TIME desc ";
							List<RowBase> dataw = DataUtil.getSelectRows(timesql, parms);
							//更新时间
							if(dataw.size()>0){
								for(int m=0;m<dataw.size();m++){
									//更新时长
									RowBase roww=dataw.get(m);
									 String id=roww.getSafeString("GUID");
									 String stime=roww.getSafeString("CONTINUE_TIME");
									 int n=Integer.parseInt(stime)+10;
									 String timestr=n+"";
									 String uptime=" update OP_ALAR_INFO set CONTINUE_TIME='"+timestr+"' where GUID='"+id+"'";
									 DataUtil.ExecuteUpdateSql(uptime, parms);
								}
							}
						}
						//没有就插入新数据
						else{
							//插入新报警
							String alarmsql="insert into OP_ALAR_INFO(GUID,TYPE,PARAM,START_TIME,ALARM_VALUE,"
									+ "STATION_CODE,STATUS,ALARM_TEXT,CONTINUE_TIME,IS_RECOVER)"
									+ "values(@GUID,@TYPE,@PARAM,@START_TIME,@ALARM_VALUE,@STATION_CODE,@STATUS,@ALARM_TEXT,@CONTINUE_TIME,@IS_RECOVER)";
							parms.clear();
							parms.add(new GlobalParameter("GUID", GUID));
							parms.add(new GlobalParameter("TYPE", "5"));
							parms.add(new GlobalParameter("PARAM", "Access"));
							parms.add(new GlobalParameter("START_TIME", F_READDATE));
							String ALARM_VALUE="";
							if(F_CARDNO.equals("84")){
								ALARM_VALUE="门长时间未关闭报警";
							}
							if(F_CARDNO.equals("85")){
								ALARM_VALUE="强行闯入报警";
							}
							parms.add(new GlobalParameter("ALARM_VALUE", ALARM_VALUE));
							parms.add(new GlobalParameter("STATION_CODE", STATION_CODE));
							parms.add(new GlobalParameter("STATUS", "0"));
							parms.add(new GlobalParameter("ALARM_TEXT", "门禁报警"));
							parms.add(new GlobalParameter("CONTINUE_TIME", "1"));
							parms.add(new GlobalParameter("IS_RECOVER", "0"));
							DataUtil.ExecuteUpdateSql(alarmsql, parms);
						}
					}
				}
			}
			
		}
		catch(Exception e){
			e.printStackTrace();
		}
	}
	
}
